{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "## 数据分析目的"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "此数据分析的目的是，根据市场销售数据，挖据畅销产品，以便制定更有效的市场策略来提升营收。\n",
    "本实战项目的目的在于练习评估数据干净和整洁度，并且基于评估结果，对数据进行清洗，从而得到可供下一步分析的数据。"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 简介"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "原始数据集记录了一家英国在线零售公司在2010年12月1日至2011年12月9日期间的所有交易情况，涵盖了该公司在全球不同国家和地区的业务数据。该公司主要销售涵盖各个场景的礼品，包括但不限于生日礼品、结婚纪念品、圣诞礼品等等。该公司的客户群体主要包括批发商和个人消费者，其中批发商占据了相当大的比例。"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 研究的问题"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "订单维度：笔单价和连带率是多少？订单金额与订单内商品件数的关系如何？\n",
    "客户维度：客单价是多少？客户消费金额与消费件数的关系如何？\n",
    "商品维度：商品的价格定位是高是低？哪种价位的商品卖得好？哪种价位的商品带来了实际上最多的销售额？\n",
    "时间维度：各月/各日的销售情况是什么走势？可能受到了什么影响？\n",
    "区域维度：客户主要来自哪几个国家？哪个国家是境外主要市场？哪个国家的客户平均消费能力最强？\n",
    "客户行为：客户的生命周期、留存情况、购买周期如何？"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 数据有54w行，8种特征分别是："
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "InvoiceNo：发票编号。为每笔订单唯一分配的6位整数。若以字母'C'开头，则表示该订单被取消。\n",
    "StockCode：产品代码。为每个产品唯一分配的编码。\n",
    "Description：产品描述。\n",
    "Quantity：数量。每笔订单中各产品分别的数量。\n",
    "InvoiceDate：发票日期和时间。每笔订单发生的日期和时间。\n",
    "UnitPrice：单价。单位产品价格，单位为英镑。\n",
    "CustomerID：客户编号。为每个客户唯一分配的5位整数。\n",
    "Country：国家。客户所在国家/地区的名称"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "outputs": [],
   "source": [
    "import pandas as pd"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:15.193306400Z",
     "start_time": "2024-03-30T08:26:15.191797200Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "outputs": [],
   "source": [
    "original_data = pd.read_csv(\"data.csv\")"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:15.570224900Z",
     "start_time": "2024-03-30T08:26:15.193306400Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "outputs": [
    {
     "data": {
      "text/plain": "  InvoiceNo StockCode                          Description  Quantity  \\\n0    536365    85123A   WHITE HANGING HEART T-LIGHT HOLDER         6   \n1    536365     71053                  WHITE METAL LANTERN         6   \n2    536365    84406B       CREAM CUPID HEARTS COAT HANGER         8   \n3    536365    84029G  KNITTED UNION FLAG HOT WATER BOTTLE         6   \n4    536365    84029E       RED WOOLLY HOTTIE WHITE HEART.         6   \n\n      InvoiceDate  UnitPrice  CustomerID         Country  \n0  12/1/2010 8:26       2.55     17850.0  United Kingdom  \n1  12/1/2010 8:26       3.39     17850.0  United Kingdom  \n2  12/1/2010 8:26       2.75     17850.0  United Kingdom  \n3  12/1/2010 8:26       3.39     17850.0  United Kingdom  \n4  12/1/2010 8:26       3.39     17850.0  United Kingdom  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>InvoiceNo</th>\n      <th>StockCode</th>\n      <th>Description</th>\n      <th>Quantity</th>\n      <th>InvoiceDate</th>\n      <th>UnitPrice</th>\n      <th>CustomerID</th>\n      <th>Country</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>536365</td>\n      <td>85123A</td>\n      <td>WHITE HANGING HEART T-LIGHT HOLDER</td>\n      <td>6</td>\n      <td>12/1/2010 8:26</td>\n      <td>2.55</td>\n      <td>17850.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>536365</td>\n      <td>71053</td>\n      <td>WHITE METAL LANTERN</td>\n      <td>6</td>\n      <td>12/1/2010 8:26</td>\n      <td>3.39</td>\n      <td>17850.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>536365</td>\n      <td>84406B</td>\n      <td>CREAM CUPID HEARTS COAT HANGER</td>\n      <td>8</td>\n      <td>12/1/2010 8:26</td>\n      <td>2.75</td>\n      <td>17850.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>536365</td>\n      <td>84029G</td>\n      <td>KNITTED UNION FLAG HOT WATER BOTTLE</td>\n      <td>6</td>\n      <td>12/1/2010 8:26</td>\n      <td>3.39</td>\n      <td>17850.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>536365</td>\n      <td>84029E</td>\n      <td>RED WOOLLY HOTTIE WHITE HEART.</td>\n      <td>6</td>\n      <td>12/1/2010 8:26</td>\n      <td>3.39</td>\n      <td>17850.0</td>\n      <td>United Kingdom</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "original_data.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:15.581827100Z",
     "start_time": "2024-03-30T08:26:15.571227400Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "##评估数据(随机抽样)"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "outputs": [
    {
     "data": {
      "text/plain": "       InvoiceNo StockCode                       Description  Quantity  \\\n292075    562539     22348       TEA BAG PLATE RED RETROSPOT        11   \n356506    568056     20682  RED RETROSPOT CHILDRENS UMBRELLA         6   \n44290     540163     22776       SWEETHEART CAKESTAND 3 TIER         1   \n180346    552315     22393       PAPERWEIGHT VINTAGE COLLAGE         6   \n276238    561036     22083         PAPER CHAIN KIT RETROSPOT         2   \n321781    565201     22840        ROUND CAKE TIN VINTAGE RED         2   \n119885    546635     84907   PINK YELLOW PATCH CUSHION COVER         1   \n59493     541290     22492           MINI PAINT SET VINTAGE         36   \n400075    571294     22633            HAND WARMER UNION JACK        10   \n50097     540546     22491       PACK OF 12 COLOURED PENCILS        12   \n\n             InvoiceDate  UnitPrice  CustomerID         Country  \n292075    8/5/2011 15:30       0.85     17220.0  United Kingdom  \n356506   9/23/2011 12:55       3.25     12705.0         Germany  \n44290     1/5/2011 11:52       9.95     13089.0  United Kingdom  \n180346    5/8/2011 16:10       2.55     12700.0          France  \n276238   7/24/2011 11:54       2.95     13137.0  United Kingdom  \n321781    9/1/2011 16:41       7.95     14907.0  United Kingdom  \n119885   3/15/2011 12:11       7.65     16392.0  United Kingdom  \n59493    1/17/2011 13:25       0.65     17288.0  United Kingdom  \n400075  10/16/2011 16:09       2.10     16987.0  United Kingdom  \n50097     1/9/2011 15:56       0.85     12766.0        Portugal  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>InvoiceNo</th>\n      <th>StockCode</th>\n      <th>Description</th>\n      <th>Quantity</th>\n      <th>InvoiceDate</th>\n      <th>UnitPrice</th>\n      <th>CustomerID</th>\n      <th>Country</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>292075</th>\n      <td>562539</td>\n      <td>22348</td>\n      <td>TEA BAG PLATE RED RETROSPOT</td>\n      <td>11</td>\n      <td>8/5/2011 15:30</td>\n      <td>0.85</td>\n      <td>17220.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>356506</th>\n      <td>568056</td>\n      <td>20682</td>\n      <td>RED RETROSPOT CHILDRENS UMBRELLA</td>\n      <td>6</td>\n      <td>9/23/2011 12:55</td>\n      <td>3.25</td>\n      <td>12705.0</td>\n      <td>Germany</td>\n    </tr>\n    <tr>\n      <th>44290</th>\n      <td>540163</td>\n      <td>22776</td>\n      <td>SWEETHEART CAKESTAND 3 TIER</td>\n      <td>1</td>\n      <td>1/5/2011 11:52</td>\n      <td>9.95</td>\n      <td>13089.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>180346</th>\n      <td>552315</td>\n      <td>22393</td>\n      <td>PAPERWEIGHT VINTAGE COLLAGE</td>\n      <td>6</td>\n      <td>5/8/2011 16:10</td>\n      <td>2.55</td>\n      <td>12700.0</td>\n      <td>France</td>\n    </tr>\n    <tr>\n      <th>276238</th>\n      <td>561036</td>\n      <td>22083</td>\n      <td>PAPER CHAIN KIT RETROSPOT</td>\n      <td>2</td>\n      <td>7/24/2011 11:54</td>\n      <td>2.95</td>\n      <td>13137.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>321781</th>\n      <td>565201</td>\n      <td>22840</td>\n      <td>ROUND CAKE TIN VINTAGE RED</td>\n      <td>2</td>\n      <td>9/1/2011 16:41</td>\n      <td>7.95</td>\n      <td>14907.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>119885</th>\n      <td>546635</td>\n      <td>84907</td>\n      <td>PINK YELLOW PATCH CUSHION COVER</td>\n      <td>1</td>\n      <td>3/15/2011 12:11</td>\n      <td>7.65</td>\n      <td>16392.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>59493</th>\n      <td>541290</td>\n      <td>22492</td>\n      <td>MINI PAINT SET VINTAGE</td>\n      <td>36</td>\n      <td>1/17/2011 13:25</td>\n      <td>0.65</td>\n      <td>17288.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>400075</th>\n      <td>571294</td>\n      <td>22633</td>\n      <td>HAND WARMER UNION JACK</td>\n      <td>10</td>\n      <td>10/16/2011 16:09</td>\n      <td>2.10</td>\n      <td>16987.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>50097</th>\n      <td>540546</td>\n      <td>22491</td>\n      <td>PACK OF 12 COLOURED PENCILS</td>\n      <td>12</td>\n      <td>1/9/2011 15:56</td>\n      <td>0.85</td>\n      <td>12766.0</td>\n      <td>Portugal</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "original_data.sample(10)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:15.598859400Z",
     "start_time": "2024-03-30T08:26:15.580828700Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 评估数据干净程度"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 541909 entries, 0 to 541908\n",
      "Data columns (total 8 columns):\n",
      " #   Column       Non-Null Count   Dtype  \n",
      "---  ------       --------------   -----  \n",
      " 0   InvoiceNo    541909 non-null  object \n",
      " 1   StockCode    541909 non-null  object \n",
      " 2   Description  540455 non-null  object \n",
      " 3   Quantity     541909 non-null  int64  \n",
      " 4   InvoiceDate  541909 non-null  object \n",
      " 5   UnitPrice    541909 non-null  float64\n",
      " 6   CustomerID   406829 non-null  float64\n",
      " 7   Country      541909 non-null  object \n",
      "dtypes: float64(2), int64(1), object(5)\n",
      "memory usage: 33.1+ MB\n"
     ]
    }
   ],
   "source": [
    "original_data.info()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:15.790155400Z",
     "start_time": "2024-03-30T08:26:15.597858200Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "从输出结果来看，数据共有541909条观察值，而Description、CustomerID变量存在缺失值。此外InvoiceDate的数据类型应为日期，CustomerID的数据类型应为字符串，应当进行数据格式转换。"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 评估确实数量"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "再了解Description存在缺失值后，根据条件提取出缺失观察值"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "outputs": [
    {
     "data": {
      "text/plain": "       InvoiceNo StockCode Description  Quantity      InvoiceDate  UnitPrice  \\\n622       536414     22139         NaN        56  12/1/2010 11:52        0.0   \n1970      536545     21134         NaN         1  12/1/2010 14:32        0.0   \n1971      536546     22145         NaN         1  12/1/2010 14:33        0.0   \n1972      536547     37509         NaN         1  12/1/2010 14:33        0.0   \n1987      536549    85226A         NaN         1  12/1/2010 14:34        0.0   \n...          ...       ...         ...       ...              ...        ...   \n535322    581199     84581         NaN        -2  12/7/2011 18:26        0.0   \n535326    581203     23406         NaN        15  12/7/2011 18:31        0.0   \n535332    581209     21620         NaN         6  12/7/2011 18:35        0.0   \n536981    581234     72817         NaN        27  12/8/2011 10:33        0.0   \n538554    581408     85175         NaN        20  12/8/2011 14:06        0.0   \n\n        CustomerID         Country  \n622            NaN  United Kingdom  \n1970           NaN  United Kingdom  \n1971           NaN  United Kingdom  \n1972           NaN  United Kingdom  \n1987           NaN  United Kingdom  \n...            ...             ...  \n535322         NaN  United Kingdom  \n535326         NaN  United Kingdom  \n535332         NaN  United Kingdom  \n536981         NaN  United Kingdom  \n538554         NaN  United Kingdom  \n\n[1454 rows x 8 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>InvoiceNo</th>\n      <th>StockCode</th>\n      <th>Description</th>\n      <th>Quantity</th>\n      <th>InvoiceDate</th>\n      <th>UnitPrice</th>\n      <th>CustomerID</th>\n      <th>Country</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>622</th>\n      <td>536414</td>\n      <td>22139</td>\n      <td>NaN</td>\n      <td>56</td>\n      <td>12/1/2010 11:52</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>1970</th>\n      <td>536545</td>\n      <td>21134</td>\n      <td>NaN</td>\n      <td>1</td>\n      <td>12/1/2010 14:32</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>1971</th>\n      <td>536546</td>\n      <td>22145</td>\n      <td>NaN</td>\n      <td>1</td>\n      <td>12/1/2010 14:33</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>1972</th>\n      <td>536547</td>\n      <td>37509</td>\n      <td>NaN</td>\n      <td>1</td>\n      <td>12/1/2010 14:33</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>1987</th>\n      <td>536549</td>\n      <td>85226A</td>\n      <td>NaN</td>\n      <td>1</td>\n      <td>12/1/2010 14:34</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>535322</th>\n      <td>581199</td>\n      <td>84581</td>\n      <td>NaN</td>\n      <td>-2</td>\n      <td>12/7/2011 18:26</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>535326</th>\n      <td>581203</td>\n      <td>23406</td>\n      <td>NaN</td>\n      <td>15</td>\n      <td>12/7/2011 18:31</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>535332</th>\n      <td>581209</td>\n      <td>21620</td>\n      <td>NaN</td>\n      <td>6</td>\n      <td>12/7/2011 18:35</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>536981</th>\n      <td>581234</td>\n      <td>72817</td>\n      <td>NaN</td>\n      <td>27</td>\n      <td>12/8/2011 10:33</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>538554</th>\n      <td>581408</td>\n      <td>85175</td>\n      <td>NaN</td>\n      <td>20</td>\n      <td>12/8/2011 14:06</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n  </tbody>\n</table>\n<p>1454 rows × 8 columns</p>\n</div>"
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "original_data[original_data[\"Description\"].isnull()]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:15.791151200Z",
     "start_time": "2024-03-30T08:26:15.651232700Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "有1454条交易数据缺失 Description 变量值\n",
    "\n",
    "从输出结果来看，这些缺失 Description 的交易数据，UnitPrice 都为0。为了验证猜想，我们增加筹选条件，看是否存在\n",
    "Description 变量缺失值 UnitPrice 不为0的数据。"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "outputs": [
    {
     "data": {
      "text/plain": "Empty DataFrame\nColumns: [InvoiceNo, StockCode, Description, Quantity, InvoiceDate, UnitPrice, CustomerID, Country]\nIndex: []",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>InvoiceNo</th>\n      <th>StockCode</th>\n      <th>Description</th>\n      <th>Quantity</th>\n      <th>InvoiceDate</th>\n      <th>UnitPrice</th>\n      <th>CustomerID</th>\n      <th>Country</th>\n    </tr>\n  </thead>\n  <tbody>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "original_data[(original_data[\"Description\"].isnull()) & (original_data[\"UnitPrice\"] != 0) ]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:15.792151700Z",
     "start_time": "2024-03-30T08:26:15.681307500Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "筛选出来的结果为0条，说明确实Description值的数据，同时也不具备有效的UnitPrice值。\n",
    "\n",
    "Description表示产品名称，UnitPrice表示产品单价，都是进行后续商品交易的重要变量，如果它们同时缺失/无效，我们认为数据无法提供有效含义，因此这些后续可以被删除"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "CustomerID 变量同样存在缺失值，因此也根据条件提取出缺失观察值，"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "outputs": [
    {
     "data": {
      "text/plain": "       InvoiceNo StockCode                      Description  Quantity  \\\n622       536414     22139                              NaN        56   \n1443      536544     21773  DECORATIVE ROSE BATHROOM BOTTLE         1   \n1444      536544     21774  DECORATIVE CATS BATHROOM BOTTLE         2   \n1445      536544     21786               POLKADOT RAIN HAT          4   \n1446      536544     21787            RAIN PONCHO RETROSPOT         2   \n...          ...       ...                              ...       ...   \n541536    581498    85099B          JUMBO BAG RED RETROSPOT         5   \n541537    581498    85099C   JUMBO  BAG BAROQUE BLACK WHITE         4   \n541538    581498     85150    LADIES & GENTLEMEN METAL SIGN         1   \n541539    581498     85174                S/4 CACTI CANDLES         1   \n541540    581498       DOT                   DOTCOM POSTAGE         1   \n\n            InvoiceDate  UnitPrice  CustomerID         Country  \n622     12/1/2010 11:52       0.00         NaN  United Kingdom  \n1443    12/1/2010 14:32       2.51         NaN  United Kingdom  \n1444    12/1/2010 14:32       2.51         NaN  United Kingdom  \n1445    12/1/2010 14:32       0.85         NaN  United Kingdom  \n1446    12/1/2010 14:32       1.66         NaN  United Kingdom  \n...                 ...        ...         ...             ...  \n541536  12/9/2011 10:26       4.13         NaN  United Kingdom  \n541537  12/9/2011 10:26       4.13         NaN  United Kingdom  \n541538  12/9/2011 10:26       4.96         NaN  United Kingdom  \n541539  12/9/2011 10:26      10.79         NaN  United Kingdom  \n541540  12/9/2011 10:26    1714.17         NaN  United Kingdom  \n\n[135080 rows x 8 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>InvoiceNo</th>\n      <th>StockCode</th>\n      <th>Description</th>\n      <th>Quantity</th>\n      <th>InvoiceDate</th>\n      <th>UnitPrice</th>\n      <th>CustomerID</th>\n      <th>Country</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>622</th>\n      <td>536414</td>\n      <td>22139</td>\n      <td>NaN</td>\n      <td>56</td>\n      <td>12/1/2010 11:52</td>\n      <td>0.00</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>1443</th>\n      <td>536544</td>\n      <td>21773</td>\n      <td>DECORATIVE ROSE BATHROOM BOTTLE</td>\n      <td>1</td>\n      <td>12/1/2010 14:32</td>\n      <td>2.51</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>1444</th>\n      <td>536544</td>\n      <td>21774</td>\n      <td>DECORATIVE CATS BATHROOM BOTTLE</td>\n      <td>2</td>\n      <td>12/1/2010 14:32</td>\n      <td>2.51</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>1445</th>\n      <td>536544</td>\n      <td>21786</td>\n      <td>POLKADOT RAIN HAT</td>\n      <td>4</td>\n      <td>12/1/2010 14:32</td>\n      <td>0.85</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>1446</th>\n      <td>536544</td>\n      <td>21787</td>\n      <td>RAIN PONCHO RETROSPOT</td>\n      <td>2</td>\n      <td>12/1/2010 14:32</td>\n      <td>1.66</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>541536</th>\n      <td>581498</td>\n      <td>85099B</td>\n      <td>JUMBO BAG RED RETROSPOT</td>\n      <td>5</td>\n      <td>12/9/2011 10:26</td>\n      <td>4.13</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>541537</th>\n      <td>581498</td>\n      <td>85099C</td>\n      <td>JUMBO  BAG BAROQUE BLACK WHITE</td>\n      <td>4</td>\n      <td>12/9/2011 10:26</td>\n      <td>4.13</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>541538</th>\n      <td>581498</td>\n      <td>85150</td>\n      <td>LADIES &amp; GENTLEMEN METAL SIGN</td>\n      <td>1</td>\n      <td>12/9/2011 10:26</td>\n      <td>4.96</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>541539</th>\n      <td>581498</td>\n      <td>85174</td>\n      <td>S/4 CACTI CANDLES</td>\n      <td>1</td>\n      <td>12/9/2011 10:26</td>\n      <td>10.79</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>541540</th>\n      <td>581498</td>\n      <td>DOT</td>\n      <td>DOTCOM POSTAGE</td>\n      <td>1</td>\n      <td>12/9/2011 10:26</td>\n      <td>1714.17</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n  </tbody>\n</table>\n<p>135080 rows × 8 columns</p>\n</div>"
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "original_data[original_data[\"CustomerID\"].isnull()]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:15.792151700Z",
     "start_time": "2024-03-30T08:26:15.694558700Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "CustomerID 表示客户编号，不是分析畅销商品的必要变量。并且从输出结果来看，有些 customerID 缺失的交易数据仍然有效，因此保留此变量为空的观察值。"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 评估数据重复性"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "根据数据变量的含义来看，虽然 InvoiceNo、StockCode 和 customerID 都是唯一标识持，但一次交易可能包含多件商品品，因此 InvoiceNo 可以存在重复，不同交易可以包含同件商品，因此StockCode 可以存在重复，顾客可以进行多次交易或下单多个商品，因此 CustomerID 也可以存在重复。\n",
    "\n",
    "那么针对此数据集，我们无需评估重复数据，"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "#评估不一致数据"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "outputs": [
    {
     "data": {
      "text/plain": "Country\nUnited Kingdom          495478\nGermany                   9495\nFrance                    8557\nEIRE                      8196\nSpain                     2533\nNetherlands               2371\nBelgium                   2069\nSwitzerland               2002\nPortugal                  1519\nAustralia                 1259\nNorway                    1086\nItaly                      803\nChannel Islands            758\nFinland                    695\nCyprus                     622\nSweden                     462\nUnspecified                446\nAustria                    401\nDenmark                    389\nJapan                      358\nPoland                     341\nIsrael                     297\nUSA                        291\nHong Kong                  288\nSingapore                  229\nIceland                    182\nCanada                     151\nGreece                     146\nMalta                      127\nUnited Arab Emirates        68\nEuropean Community          61\nRSA                         58\nLebanon                     45\nLithuania                   35\nBrazil                      32\nCzech Republic              30\nBahrain                     19\nSaudi Arabia                10\nName: count, dtype: int64"
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "original_data[\"Country\"].value_counts()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:15.793151400Z",
     "start_time": "2024-03-30T08:26:15.716286600Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "从Country 变量值来看，\"USA”、\"United States”均在表示美国，“United Kingdom”、“Ux”、\"U.K,”均在表\n",
    "示英国，因此应该在清洗步骤对这些值进行统一，只保留一个指代值。"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 评估无效或者错误数据"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "outputs": [
    {
     "data": {
      "text/plain": "            Quantity      UnitPrice     CustomerID\ncount  541909.000000  541909.000000  406829.000000\nmean        9.552250       4.611114   15287.690570\nstd       218.081158      96.759853    1713.600303\nmin    -80995.000000  -11062.060000   12346.000000\n25%         1.000000       1.250000   13953.000000\n50%         3.000000       2.080000   15152.000000\n75%        10.000000       4.130000   16791.000000\nmax     80995.000000   38970.000000   18287.000000",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>Quantity</th>\n      <th>UnitPrice</th>\n      <th>CustomerID</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>count</th>\n      <td>541909.000000</td>\n      <td>541909.000000</td>\n      <td>406829.000000</td>\n    </tr>\n    <tr>\n      <th>mean</th>\n      <td>9.552250</td>\n      <td>4.611114</td>\n      <td>15287.690570</td>\n    </tr>\n    <tr>\n      <th>std</th>\n      <td>218.081158</td>\n      <td>96.759853</td>\n      <td>1713.600303</td>\n    </tr>\n    <tr>\n      <th>min</th>\n      <td>-80995.000000</td>\n      <td>-11062.060000</td>\n      <td>12346.000000</td>\n    </tr>\n    <tr>\n      <th>25%</th>\n      <td>1.000000</td>\n      <td>1.250000</td>\n      <td>13953.000000</td>\n    </tr>\n    <tr>\n      <th>50%</th>\n      <td>3.000000</td>\n      <td>2.080000</td>\n      <td>15152.000000</td>\n    </tr>\n    <tr>\n      <th>75%</th>\n      <td>10.000000</td>\n      <td>4.130000</td>\n      <td>16791.000000</td>\n    </tr>\n    <tr>\n      <th>max</th>\n      <td>80995.000000</td>\n      <td>38970.000000</td>\n      <td>18287.000000</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "original_data.describe()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:15.939043500Z",
     "start_time": "2024-03-30T08:26:15.740151900Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "outputs": [
    {
     "data": {
      "text/plain": "       InvoiceNo StockCode                       Description  Quantity  \\\n141      C536379         D                          Discount        -1   \n154      C536383    35004C   SET OF 3 COLOURED  FLYING DUCKS        -1   \n235      C536391     22556    PLASTERS IN TIN CIRCUS PARADE        -12   \n236      C536391     21984  PACK OF 12 PINK PAISLEY TISSUES        -24   \n237      C536391     21983  PACK OF 12 BLUE PAISLEY TISSUES        -24   \n...          ...       ...                               ...       ...   \n540449   C581490     23144   ZINC T-LIGHT HOLDER STARS SMALL       -11   \n541541   C581499         M                            Manual        -1   \n541715   C581568     21258        VICTORIAN SEWING BOX LARGE        -5   \n541716   C581569     84978  HANGING HEART JAR T-LIGHT HOLDER        -1   \n541717   C581569     20979     36 PENCILS TUBE RED RETROSPOT        -5   \n\n            InvoiceDate  UnitPrice  CustomerID         Country  \n141      12/1/2010 9:41      27.50     14527.0  United Kingdom  \n154      12/1/2010 9:49       4.65     15311.0  United Kingdom  \n235     12/1/2010 10:24       1.65     17548.0  United Kingdom  \n236     12/1/2010 10:24       0.29     17548.0  United Kingdom  \n237     12/1/2010 10:24       0.29     17548.0  United Kingdom  \n...                 ...        ...         ...             ...  \n540449   12/9/2011 9:57       0.83     14397.0  United Kingdom  \n541541  12/9/2011 10:28     224.69     15498.0  United Kingdom  \n541715  12/9/2011 11:57      10.95     15311.0  United Kingdom  \n541716  12/9/2011 11:58       1.25     17315.0  United Kingdom  \n541717  12/9/2011 11:58       1.25     17315.0  United Kingdom  \n\n[10624 rows x 8 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>InvoiceNo</th>\n      <th>StockCode</th>\n      <th>Description</th>\n      <th>Quantity</th>\n      <th>InvoiceDate</th>\n      <th>UnitPrice</th>\n      <th>CustomerID</th>\n      <th>Country</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>141</th>\n      <td>C536379</td>\n      <td>D</td>\n      <td>Discount</td>\n      <td>-1</td>\n      <td>12/1/2010 9:41</td>\n      <td>27.50</td>\n      <td>14527.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>154</th>\n      <td>C536383</td>\n      <td>35004C</td>\n      <td>SET OF 3 COLOURED  FLYING DUCKS</td>\n      <td>-1</td>\n      <td>12/1/2010 9:49</td>\n      <td>4.65</td>\n      <td>15311.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>235</th>\n      <td>C536391</td>\n      <td>22556</td>\n      <td>PLASTERS IN TIN CIRCUS PARADE</td>\n      <td>-12</td>\n      <td>12/1/2010 10:24</td>\n      <td>1.65</td>\n      <td>17548.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>236</th>\n      <td>C536391</td>\n      <td>21984</td>\n      <td>PACK OF 12 PINK PAISLEY TISSUES</td>\n      <td>-24</td>\n      <td>12/1/2010 10:24</td>\n      <td>0.29</td>\n      <td>17548.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>237</th>\n      <td>C536391</td>\n      <td>21983</td>\n      <td>PACK OF 12 BLUE PAISLEY TISSUES</td>\n      <td>-24</td>\n      <td>12/1/2010 10:24</td>\n      <td>0.29</td>\n      <td>17548.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>540449</th>\n      <td>C581490</td>\n      <td>23144</td>\n      <td>ZINC T-LIGHT HOLDER STARS SMALL</td>\n      <td>-11</td>\n      <td>12/9/2011 9:57</td>\n      <td>0.83</td>\n      <td>14397.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>541541</th>\n      <td>C581499</td>\n      <td>M</td>\n      <td>Manual</td>\n      <td>-1</td>\n      <td>12/9/2011 10:28</td>\n      <td>224.69</td>\n      <td>15498.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>541715</th>\n      <td>C581568</td>\n      <td>21258</td>\n      <td>VICTORIAN SEWING BOX LARGE</td>\n      <td>-5</td>\n      <td>12/9/2011 11:57</td>\n      <td>10.95</td>\n      <td>15311.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>541716</th>\n      <td>C581569</td>\n      <td>84978</td>\n      <td>HANGING HEART JAR T-LIGHT HOLDER</td>\n      <td>-1</td>\n      <td>12/9/2011 11:58</td>\n      <td>1.25</td>\n      <td>17315.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>541717</th>\n      <td>C581569</td>\n      <td>20979</td>\n      <td>36 PENCILS TUBE RED RETROSPOT</td>\n      <td>-5</td>\n      <td>12/9/2011 11:58</td>\n      <td>1.25</td>\n      <td>17315.0</td>\n      <td>United Kingdom</td>\n    </tr>\n  </tbody>\n</table>\n<p>10624 rows × 8 columns</p>\n</div>"
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "original_data[original_data[\"Quantity\"] < 0]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:15.940105800Z",
     "start_time": "2024-03-30T08:26:15.840769800Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "outputs": [
    {
     "data": {
      "text/plain": "       InvoiceNo StockCode Description  Quantity      InvoiceDate  UnitPrice  \\\n2406      536589     21777         NaN       -10  12/1/2010 16:50        0.0   \n4347      536764    84952C         NaN       -38  12/2/2010 14:42        0.0   \n7188      536996     22712         NaN       -20  12/3/2010 15:30        0.0   \n7189      536997     22028         NaN       -20  12/3/2010 15:30        0.0   \n7190      536998     85067         NaN        -6  12/3/2010 15:30        0.0   \n...          ...       ...         ...       ...              ...        ...   \n535333    581210     23395       check       -26  12/7/2011 18:36        0.0   \n535335    581212     22578        lost     -1050  12/7/2011 18:38        0.0   \n535336    581213     22576       check       -30  12/7/2011 18:38        0.0   \n536908    581226     23090     missing      -338   12/8/2011 9:56        0.0   \n538919    581422     23169     smashed      -235  12/8/2011 15:24        0.0   \n\n        CustomerID         Country  \n2406           NaN  United Kingdom  \n4347           NaN  United Kingdom  \n7188           NaN  United Kingdom  \n7189           NaN  United Kingdom  \n7190           NaN  United Kingdom  \n...            ...             ...  \n535333         NaN  United Kingdom  \n535335         NaN  United Kingdom  \n535336         NaN  United Kingdom  \n536908         NaN  United Kingdom  \n538919         NaN  United Kingdom  \n\n[1336 rows x 8 columns]",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>InvoiceNo</th>\n      <th>StockCode</th>\n      <th>Description</th>\n      <th>Quantity</th>\n      <th>InvoiceDate</th>\n      <th>UnitPrice</th>\n      <th>CustomerID</th>\n      <th>Country</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>2406</th>\n      <td>536589</td>\n      <td>21777</td>\n      <td>NaN</td>\n      <td>-10</td>\n      <td>12/1/2010 16:50</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>4347</th>\n      <td>536764</td>\n      <td>84952C</td>\n      <td>NaN</td>\n      <td>-38</td>\n      <td>12/2/2010 14:42</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>7188</th>\n      <td>536996</td>\n      <td>22712</td>\n      <td>NaN</td>\n      <td>-20</td>\n      <td>12/3/2010 15:30</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>7189</th>\n      <td>536997</td>\n      <td>22028</td>\n      <td>NaN</td>\n      <td>-20</td>\n      <td>12/3/2010 15:30</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>7190</th>\n      <td>536998</td>\n      <td>85067</td>\n      <td>NaN</td>\n      <td>-6</td>\n      <td>12/3/2010 15:30</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>...</th>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n      <td>...</td>\n    </tr>\n    <tr>\n      <th>535333</th>\n      <td>581210</td>\n      <td>23395</td>\n      <td>check</td>\n      <td>-26</td>\n      <td>12/7/2011 18:36</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>535335</th>\n      <td>581212</td>\n      <td>22578</td>\n      <td>lost</td>\n      <td>-1050</td>\n      <td>12/7/2011 18:38</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>535336</th>\n      <td>581213</td>\n      <td>22576</td>\n      <td>check</td>\n      <td>-30</td>\n      <td>12/7/2011 18:38</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>536908</th>\n      <td>581226</td>\n      <td>23090</td>\n      <td>missing</td>\n      <td>-338</td>\n      <td>12/8/2011 9:56</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>538919</th>\n      <td>581422</td>\n      <td>23169</td>\n      <td>smashed</td>\n      <td>-235</td>\n      <td>12/8/2011 15:24</td>\n      <td>0.0</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n  </tbody>\n</table>\n<p>1336 rows × 8 columns</p>\n</div>"
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "original_data[(original_data[\"Quantity\"] < 0) & (original_data[\"InvoiceNo\"].astype(str).str[0] != \"C\")]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:16.174834Z",
     "start_time": "2024-03-30T08:26:15.864819Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "outputs": [
    {
     "data": {
      "text/plain": "Empty DataFrame\nColumns: [InvoiceNo, StockCode, Description, Quantity, InvoiceDate, UnitPrice, CustomerID, Country]\nIndex: []",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>InvoiceNo</th>\n      <th>StockCode</th>\n      <th>Description</th>\n      <th>Quantity</th>\n      <th>InvoiceDate</th>\n      <th>UnitPrice</th>\n      <th>CustomerID</th>\n      <th>Country</th>\n    </tr>\n  </thead>\n  <tbody>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "original_data[(original_data[\"Quantity\"] < 0) & (original_data[\"InvoiceNo\"].astype(str).str[0] != \"C\") & (original_data[\"UnitPrice\"] != 0)]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:16.248825400Z",
     "start_time": "2024-03-30T08:26:15.989529400Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "outputs": [
    {
     "data": {
      "text/plain": "       InvoiceNo StockCode      Description  Quantity      InvoiceDate  \\\n299983   A563186         B  Adjust bad debt         1  8/12/2011 14:51   \n299984   A563187         B  Adjust bad debt         1  8/12/2011 14:52   \n\n        UnitPrice  CustomerID         Country  \n299983  -11062.06         NaN  United Kingdom  \n299984  -11062.06         NaN  United Kingdom  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>InvoiceNo</th>\n      <th>StockCode</th>\n      <th>Description</th>\n      <th>Quantity</th>\n      <th>InvoiceDate</th>\n      <th>UnitPrice</th>\n      <th>CustomerID</th>\n      <th>Country</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>299983</th>\n      <td>A563186</td>\n      <td>B</td>\n      <td>Adjust bad debt</td>\n      <td>1</td>\n      <td>8/12/2011 14:51</td>\n      <td>-11062.06</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>299984</th>\n      <td>A563187</td>\n      <td>B</td>\n      <td>Adjust bad debt</td>\n      <td>1</td>\n      <td>8/12/2011 14:52</td>\n      <td>-11062.06</td>\n      <td>NaN</td>\n      <td>United Kingdom</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "original_data[(original_data[\"UnitPrice\"] < 0)]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:16.249846100Z",
     "start_time": "2024-03-30T08:26:16.117122Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "## 清洗数据"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "根据前面评估部分得到的结论，我们需要进行的数据清理包括:\n",
    "\n",
    "把 InvoiceDate 变量的数据类型转换为为日期时间\n",
    "把 CustomerID 变量的数据类型转换为字符串\n",
    "把 Description 变量缺失的观察值删除\n",
    "把 Country 变量值\"USA\"替换为\"United States”\n",
    "把 Country 变量值“UK”、\"U.K.\"替换为“United Kingdom\n",
    "把 Quantity 变量值为负数的观察值删除\n",
    "把 UnitPrice 变量值为负数的观察值副除"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "outputs": [
    {
     "data": {
      "text/plain": "  InvoiceNo StockCode                          Description  Quantity  \\\n0    536365    85123A   WHITE HANGING HEART T-LIGHT HOLDER         6   \n1    536365     71053                  WHITE METAL LANTERN         6   \n2    536365    84406B       CREAM CUPID HEARTS COAT HANGER         8   \n3    536365    84029G  KNITTED UNION FLAG HOT WATER BOTTLE         6   \n4    536365    84029E       RED WOOLLY HOTTIE WHITE HEART.         6   \n\n      InvoiceDate  UnitPrice  CustomerID         Country  \n0  12/1/2010 8:26       2.55     17850.0  United Kingdom  \n1  12/1/2010 8:26       3.39     17850.0  United Kingdom  \n2  12/1/2010 8:26       2.75     17850.0  United Kingdom  \n3  12/1/2010 8:26       3.39     17850.0  United Kingdom  \n4  12/1/2010 8:26       3.39     17850.0  United Kingdom  ",
      "text/html": "<div>\n<style scoped>\n    .dataframe tbody tr th:only-of-type {\n        vertical-align: middle;\n    }\n\n    .dataframe tbody tr th {\n        vertical-align: top;\n    }\n\n    .dataframe thead th {\n        text-align: right;\n    }\n</style>\n<table border=\"1\" class=\"dataframe\">\n  <thead>\n    <tr style=\"text-align: right;\">\n      <th></th>\n      <th>InvoiceNo</th>\n      <th>StockCode</th>\n      <th>Description</th>\n      <th>Quantity</th>\n      <th>InvoiceDate</th>\n      <th>UnitPrice</th>\n      <th>CustomerID</th>\n      <th>Country</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>536365</td>\n      <td>85123A</td>\n      <td>WHITE HANGING HEART T-LIGHT HOLDER</td>\n      <td>6</td>\n      <td>12/1/2010 8:26</td>\n      <td>2.55</td>\n      <td>17850.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>536365</td>\n      <td>71053</td>\n      <td>WHITE METAL LANTERN</td>\n      <td>6</td>\n      <td>12/1/2010 8:26</td>\n      <td>3.39</td>\n      <td>17850.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>536365</td>\n      <td>84406B</td>\n      <td>CREAM CUPID HEARTS COAT HANGER</td>\n      <td>8</td>\n      <td>12/1/2010 8:26</td>\n      <td>2.75</td>\n      <td>17850.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>536365</td>\n      <td>84029G</td>\n      <td>KNITTED UNION FLAG HOT WATER BOTTLE</td>\n      <td>6</td>\n      <td>12/1/2010 8:26</td>\n      <td>3.39</td>\n      <td>17850.0</td>\n      <td>United Kingdom</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>536365</td>\n      <td>84029E</td>\n      <td>RED WOOLLY HOTTIE WHITE HEART.</td>\n      <td>6</td>\n      <td>12/1/2010 8:26</td>\n      <td>3.39</td>\n      <td>17850.0</td>\n      <td>United Kingdom</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_data = original_data.copy()\n",
    "cleaned_data.head()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:16.299230900Z",
     "start_time": "2024-03-30T08:26:16.133406700Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "把InvoiceDate变量的数据类型转换为日期时间类型"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "outputs": [
    {
     "data": {
      "text/plain": "0        2010-12-01 08:26:00\n1        2010-12-01 08:26:00\n2        2010-12-01 08:26:00\n3        2010-12-01 08:26:00\n4        2010-12-01 08:26:00\n                 ...        \n541904   2011-12-09 12:50:00\n541905   2011-12-09 12:50:00\n541906   2011-12-09 12:50:00\n541907   2011-12-09 12:50:00\n541908   2011-12-09 12:50:00\nName: InvoiceDate, Length: 541909, dtype: datetime64[ns]"
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_data[\"InvoiceDate\"] = pd.to_datetime(cleaned_data[\"InvoiceDate\"])\n",
    "cleaned_data[\"InvoiceDate\"]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:16.391749300Z",
     "start_time": "2024-03-30T08:26:16.163832900Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "outputs": [
    {
     "data": {
      "text/plain": "0         17850.0\n1         17850.0\n2         17850.0\n3         17850.0\n4         17850.0\n           ...   \n541904    12680.0\n541905    12680.0\n541906    12680.0\n541907    12680.0\n541908    12680.0\nName: CustomerID, Length: 541909, dtype: object"
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_data[\"CustomerID\"] = cleaned_data[\"CustomerID\"].astype(str)\n",
    "cleaned_data[\"CustomerID\"]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:16.692591600Z",
     "start_time": "2024-03-30T08:26:16.246827200Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "去除0"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "outputs": [
    {
     "data": {
      "text/plain": "0         17850\n1         17850\n2         17850\n3         17850\n4         17850\n          ...  \n541904    12680\n541905    12680\n541906    12680\n541907    12680\n541908    12680\nName: CustomerID, Length: 541909, dtype: object"
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_data[\"CustomerID\"] = cleaned_data[\"CustomerID\"].str.slice(0, -2)\n",
    "cleaned_data[\"CustomerID\"]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:16.729899800Z",
     "start_time": "2024-03-30T08:26:16.423893600Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "把 Description 变量缺失的观察值删除"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "outputs": [],
   "source": [
    "cleaned_data = cleaned_data.dropna(subset=[\"Description\"])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:16.816379700Z",
     "start_time": "2024-03-30T08:26:16.526650900Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "outputs": [
    {
     "data": {
      "text/plain": "0"
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cleaned_data[\"Description\"].isnull().sum()"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:16.860765200Z",
     "start_time": "2024-03-30T08:26:16.586406100Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "把 Country 变量值\"USA\"替换为\"United States”"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "outputs": [],
   "source": [
    "cleaned_data[\"Country\"] = cleaned_data[\"Country\"].replace({\"USA\": \"United States\"})"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:16.937241300Z",
     "start_time": "2024-03-30T08:26:16.600797600Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "outputs": [
    {
     "data": {
      "text/plain": "0"
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(cleaned_data[cleaned_data[\"Country\"] == \"USA\"])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:16.989783800Z",
     "start_time": "2024-03-30T08:26:16.672632500Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "outputs": [],
   "source": [
    "cleaned_data[\"Country\"] = cleaned_data[\"Country\"].replace({\"UK\": \"United Kingdom\", \"U.K\": \"United Kingdom\"})"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:16.991783Z",
     "start_time": "2024-03-30T08:26:16.678597200Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "outputs": [
    {
     "data": {
      "text/plain": "0"
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(cleaned_data[cleaned_data[\"Country\"] == \"UK\"])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:16.992783400Z",
     "start_time": "2024-03-30T08:26:16.728902Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "outputs": [
    {
     "data": {
      "text/plain": "0"
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(cleaned_data[cleaned_data[\"Country\"] == \"U.K.\"])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:17.062320800Z",
     "start_time": "2024-03-30T08:26:16.749822100Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "outputs": [],
   "source": [
    "cleaned_data = cleaned_data[cleaned_data[\"Quantity\"] >= 0]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:17.064304300Z",
     "start_time": "2024-03-30T08:26:16.771839400Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "outputs": [
    {
     "data": {
      "text/plain": "0"
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(cleaned_data[cleaned_data[\"Quantity\"] < 0])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:17.064304300Z",
     "start_time": "2024-03-30T08:26:16.834440200Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "outputs": [],
   "source": [
    "cleaned_data = cleaned_data[cleaned_data[\"UnitPrice\"] >= 0]"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:17.073302400Z",
     "start_time": "2024-03-30T08:26:16.841299600Z"
    }
   }
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "outputs": [
    {
     "data": {
      "text/plain": "0"
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(cleaned_data[cleaned_data[\"UnitPrice\"] < 0])"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:17.074303700Z",
     "start_time": "2024-03-30T08:26:16.937241300Z"
    }
   }
  },
  {
   "cell_type": "markdown",
   "source": [
    "#保存清理完成的数据，保存为新的文件名"
   ],
   "metadata": {
    "collapsed": false
   }
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "outputs": [],
   "source": [
    "cleaned_data.to_csv(\"e_commerce_cleaned_Data.csv\", index=False)"
   ],
   "metadata": {
    "collapsed": false,
    "ExecuteTime": {
     "end_time": "2024-03-30T08:26:18.217899200Z",
     "start_time": "2024-03-30T08:26:16.944785Z"
    }
   }
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}
